﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using NPOI;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;

namespace ExcelExport
{
    class Program
    {
        static void Main(string[] args)
        {

            #region Test 1.NPOI Demo
            //NPOIExport("Export Simulation",@"D:\test.xls"); 
            #endregion

            #region Test2.General Advanced Excel Export
            //var dt = new DataTable();
            ////模拟20行20列数据
            //for (var i = 0; i < 3; i++)
            //{
            //    dt.Columns.Add(i.ToString());
            //}
            //for (var j = 0; j < 100; j++)
            //{
            //    dt.Rows.Add(new object[] { "姓名(不分先后)", "职位", "几把刷子" });
            //}
            //NPOIExportAdvanced("军情七处8月枪毙名单", @"D:\testAdvanced.xls", dt); 
            #endregion

            #region Test3.EPPlus Demo
            //EPPlusExport("","",null); 
            #endregion

            #region Test4.EPPlus AdvancedDemo
            //EPPlusExportAdvanced("安庆菜市场谷物价格表",@"D:\EPPlusAdvanced.xlsx");
            #endregion

            var dt = new DataTable();
            //模拟20行20列数据
            for (var i = 0; i < 3; i++)
            {
                dt.Columns.Add(i.ToString());
                dt.Columns[i].ColumnName = "八仙过海这是标题" + i.ToString();

            }
            for (var j = 0; j < 100; j++)
            {
                dt.Rows.Add(new object[] { "姓名(不分先后)", "职位", "几把刷子" });
            }
            GeneralEPPlusExport("兵不厌诈",@"D:\EPPLUSADV.xlsx",dt);
        }

        /// <summary>
        /// Export First Thing First
        /// </summary>
        /// <param name="title"></param>
        /// <param name="path"></param>
        /// <param name="dt"></param>
        public static void NPOIExport(string title,string path,DataTable dt)
        {
            var book = new HSSFWorkbook();
            var table = book.CreateSheet(title);

            for(int i = 0; i < 20; i++)
            {
                var row = table.CreateRow(i);

                for(int j = 0;j < 20; j++)
                {
                    var cell = row.CreateCell(j);
                    cell.SetCellValue(j);
                }
            }

            using(var fs = File.OpenWrite(path))
            {
                book.Write(fs);
                Console.WriteLine("导出完成");
                Console.ReadLine();
            }
        }

        /// <summary>
        /// Export With Style
        /// </summary>
        /// <param name="title"></param>
        /// <param name="path"></param>
        /// <param name="dt"></param>
        public static void NPOIExportAdvanced(string title,string path,DataTable dt)
        {
            var workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(title);

            var dateStyle = workbook.CreateCellStyle();
            var format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");


            var colWidth = new int[dt.Columns.Count];
            //Default ColumnWidth
            foreach(DataColumn item in dt.Columns)
            {
                colWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length ;
            }

            //Resize ColumnWIdth
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    int widthTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
                    if(colWidth[j] < widthTemp)
                    {
                        colWidth[j] = widthTemp;
                    }
                }
            }

            int rowIndex = 0;

            foreach(DataRow row in dt.Rows)
            {
                if (rowIndex == 0)
                {
                    //tableHeader Style
                    var tableRow = sheet.CreateRow(0);
                    tableRow.HeightInPoints = 25;
                    tableRow.CreateCell(0).SetCellValue(title);

                    ICellStyle tableHeaderStyle = workbook.CreateCellStyle();
                    tableHeaderStyle.Alignment = HorizontalAlignment.Center;
                    tableHeaderStyle.VerticalAlignment = VerticalAlignment.Center;
                    tableHeaderStyle.FillForegroundColor = (short)11;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    tableHeaderStyle.SetFont(font);
                    tableRow.GetCell(0).CellStyle = tableHeaderStyle;
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));

                    //ColumnHeader Style
                    var headerRow = sheet.CreateRow(1);
                    ICellStyle headerStyle = workbook.CreateCellStyle();
                    headerStyle.Alignment = HorizontalAlignment.Center;           //水平居中
                    headerStyle.VerticalAlignment = VerticalAlignment.Center;     //垂直居中
                    IFont headerFont = workbook.CreateFont();
                    headerFont.FontHeightInPoints = 20;
                    headerFont.Boldweight = 700;
                    headerStyle.SetFont(headerFont);

                    //HeaderCell Assignment
                    foreach (DataColumn column in dt.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        headerRow.GetCell(column.Ordinal).CellStyle = headerStyle;
                        sheet.SetColumnWidth(column.Ordinal, (colWidth[column.Ordinal] + 1) * 256);
                    }

                    rowIndex = 2; 
                }

                var dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    var newCell = dataRow.CreateCell(column.Ordinal);
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String":       //字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime":     //日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;      //格式化显示
                            break;
                        case "System.Boolean":      //布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16":        //整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal":      //浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull":       //空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                rowIndex++;
            }
            //Auto ColumnWidth
            for (int i = 0; i <= dt.Columns.Count; i++)
                sheet.AutoSizeColumn(i, true);

            using (var fs = File.OpenWrite(path))
            {
                workbook.Write(fs);
            }
        }

        public static void EPPlusExport(string title,string path,DataTable dt)
        {
            //All of Code should Within 'Using' Region!
            using (ExcelPackage package = new ExcelPackage(new FileInfo(@"D:\EPPlusTest.xlsx")))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Test1");
                sheet.Cells[1, 1].Value = "名称";
                sheet.Cells[1, 2].Value = "价格";
                sheet.Cells[1, 3].Value = "销量";

                sheet.Cells[2, 1].Value = "大米";
                sheet.Cells[2, 2].Value = "5.6";
                sheet.Cells[2, 3].Value = "36500";

                sheet.Cells[3, 1].Value = "玉米";
                sheet.Cells[3, 2].Value = "3.8";
                sheet.Cells[3, 3].Value = "54300";

                sheet.Cells[4, 1].Value = "黑米";
                sheet.Cells[4, 2].Value = "7.6";
                sheet.Cells[4, 3].Value = "12800";

                sheet.Cells[5, 1].Value = "糯米";
                sheet.Cells[5, 2].Value = "4.5";
                sheet.Cells[5, 3].Value = "9800";

                package.Save();
            }
        }

        public static void EPPlusExportAdvanced(string title,string path)
        {
            //All of Code should Within 'Using' Region!
            using (ExcelPackage package = new ExcelPackage(new FileInfo(path)))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Test1");

                sheet.Cells[1, 1, 1, 3].Merge = true;
                sheet.Cells[1, 1].Value = title;
                sheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 122, 204));
                sheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                sheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                sheet.Cells[1, 1].Style.Font.Bold = true;
                sheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                sheet.Cells[1, 1].Style.Font.Name = "宋体";
                sheet.Cells[1, 1].Style.Font.Size = 14;
                sheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(204, 235, 232));

                sheet.Cells[2,1,2,3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                sheet.Cells[2,1,2,3].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                sheet.Cells[2,1,2,3].Style.Font.Bold = true;
                sheet.Cells[2,1,2,3].Style.Fill.PatternType = ExcelFillStyle.Solid;
                sheet.Cells[2,1,2,3].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(214, 219, 233));
                sheet.Cells[2,1,2,3].Style.Font.Name = "宋体";
                sheet.Cells[2,1,2,3].Style.Font.Size = 12;

                sheet.Cells[2, 1].Value = "名称";
                sheet.Cells[2, 2].Value = "价格";
                sheet.Cells[2, 3].Value = "销量";

                sheet.Cells[3, 1].Value = "大米";
                sheet.Cells[3, 2].Value = "5.6";
                sheet.Cells[3, 3].Value = "36500";

                sheet.Cells[4, 1].Value = "玉米";
                sheet.Cells[4, 2].Value = "3.8";
                sheet.Cells[4, 3].Value = "54300";

                sheet.Cells[5, 1].Value = "黑米";
                sheet.Cells[5, 2].Value = "7.6";
                sheet.Cells[5, 3].Value = "12800";

                sheet.Cells[6, 1].Value = "糯米";
                sheet.Cells[6, 2].Value = "4.5";
                sheet.Cells[6, 3].Value = "9800";

                package.Save();
            }
        }

        public static void GeneralEPPlusExport(string title,string path,DataTable dt)
        {
            if (File.Exists(path))
            {
                File.Delete(path);
            }

            using (ExcelPackage package = new ExcelPackage(new FileInfo(path)))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Export1");
                int columns = dt.Columns.Count;
                int rows = dt.Rows.Count;

                //Set TableHeader Style
                sheet.Cells[1, 1, 1, columns].Merge = true;
                sheet.Cells[1, 1].Value = title;
                sheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 122, 204));
                sheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                sheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                sheet.Cells[1, 1].Style.Font.Bold = true;
                sheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                sheet.Cells[1, 1].Style.Font.Name = "宋体";
                sheet.Cells[1, 1].Style.Font.Size = 14;
                
                sheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(204, 235, 232));

                //Set ColumnHeader Style
                sheet.Cells[2, 1, 2, columns].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                sheet.Cells[2, 1, 2, columns].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                sheet.Cells[2, 1, 2, columns].Style.Font.Bold = true;
                sheet.Cells[2, 1, 2, columns].Style.Fill.PatternType = ExcelFillStyle.Solid;
                sheet.Cells[2, 1, 2, columns].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(214, 219, 233));
                sheet.Cells[2, 1, 2, columns].Style.Font.Name = "宋体";
                sheet.Cells[2, 1, 2, columns].Style.Font.Size = 12;
                //sheet.Cells[2, 1, 2, columns].AutoFitColumns();
                foreach(DataColumn column in dt.Columns)
                {
                    sheet.Cells[2, column.Ordinal + 1].Value = column.ColumnName;
                }

                //Recursive Assignment
                for(int i = 0; i < rows; i++)
                {
                    foreach(DataColumn column in dt.Columns)
                    {
                        sheet.Cells[3 + i, column.Ordinal + 1].Value = dt.Rows[i][column.Ordinal].ToString();
                    }
                }

                ////Auto Alignment
                //for (int i = 1; i <= sheet.Dimension.End.Column; i++)
                //{
                //    sheet.Column(i).AutoFit();
                //}
                sheet.Cells.AutoFitColumns();

                package.Save();
            }
        }
    }
}
